from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment
from openpyxl.drawing.image import Image
from openpyxl.worksheet.page import PageMargins

# 新建一个Excel文档（初始化）
workbook = Workbook()
# 创建一个sheet表，新建的多个表默认插在后面
sheet = workbook.create_sheet("cover")
# 定义表头
sheet.append(['宁波德业储能科技有限公司'])
# 合并单元格
sheet.merge_cells('A1:L3')
# 设置字体
sheet['A1'].font = Font(name='微软雅黑', size='28', bold=True, italic=False, color=colors.BLACK)
# 对齐方式  换行:wrap_text=True
sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')
# 行高
sheet.row_dimensions[1].height = 26.25
# 列宽
sheet.column_dimensions['A'].width = 0.92
# 边框
# 设置边框四个方向的线条种类
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
# 将各方向线条作为参数传入Border方法
sheet['A1'].border = Border(left=left, right=right, top=top, bottom=bottom)

# 添加公司logo
logo = Image("D:\python-project\Excel\deyelogo.png")
logo.width = 113
logo.height = 38
sheet.add_image(logo, "A1")

# 创建无边框样式
no_border = Border(
    top=Side(border_style=None),
    right=Side(border_style=None),
    bottom=Side(border_style=None),
    left=Side(border_style=None)
)

# 1. 设置 A1 到 L35 范围内的单元格为无框线
for row in sheet['A1:L35']:
    for cell in row:
        cell.border = no_border

# 创建边框样式：仅给 L 列右侧和 35 行下方添加边框
right_border = Border(right=Side(border_style="thin"))
bottom_border = Border(bottom=Side(border_style="thin"))
# 只为 L 列的右侧加边框
for row in range(1, 36):  # 遍历 A1 到 A35 行
    sheet.cell(row=row, column=12).border = right_border  # L 列（列 12）
# 只为 35 行的下方加边框
for col in range(1, 13):  # 遍历 1 到 L 列
    sheet.cell(row=35, column=col).border = bottom_border  # 第 35 行
# 创建右框线和下框线样式
right_and_bottom_border = Border(
    right=Side(border_style="thin"),  # 右边框
    bottom=Side(border_style="thin")  # 下边框
)
# 给 L35 单元格加上右边框和下边框
sheet.cell(row=35, column=12).border = right_and_bottom_border

# 添加数据到 C5 并合并 C5:K5
sheet['C5'] = '软件功能测试报告'
sheet.merge_cells('C5:K5')
sheet['C5'].font = Font(name='微软雅黑', size='56', bold=True, italic=False, color='000000')
sheet['C5'].alignment = Alignment(horizontal='center', vertical='bottom')
sheet.row_dimensions[5].height = 98.1
sheet.column_dimensions['C'].width = 8.38

# 添加数据到 C6 并合并 C6:K6
sheet['C6'] = 'Software Function Testing Report'
sheet.merge_cells('C6:K6')
sheet['C6'].font = Font(name='Arial', size='24', bold=True, italic=False, color='000000')
sheet['C6'].alignment = Alignment(horizontal='center', vertical='top')
sheet.row_dimensions[6].height = 43.5

# 设置列宽（直接使用列索引）
for col in range(3, 12):  # C=3, K=11
    column_letter = chr(64 + col)  # 使用列索引转为字母
    sheet.column_dimensions[column_letter].width = 8.38

# 设置双实线边框
double_line = Side(style='double', color='000000')
no_line = Side(style='none')

# 遍历 C5:K6 范围的每个单元格，根据其位置设置边框
for row in range(5, 7):  # 行 5 到 6
    for col in range(3, 12):  # 列 C(3) 到 K(11)
        cell = sheet.cell(row=row, column=col)
        cell.border = Border(
            left=double_line if col == 3 else no_line,
            right=double_line if col == 11 else no_line,
            top=double_line if row == 5 else no_line,
            bottom=double_line if row == 6 else no_line,
        )

# 产品名称
sheet['D8'] = '产 品 名 称：Product name'
sheet.merge_cells('D8:E9')
sheet['D8'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D8'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[8].height = 20.1
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D8'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F8:I9')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=9, column=col).border = Border(bottom=bottom_line)
sheet['F8'].border = Border(bottom=bottom_line)
sheet['F8'].alignment = Alignment(horizontal='center', vertical='center')

# 型号规格
sheet['D11'] = '型 号 规 格：Model/SPEC.'
sheet.merge_cells('D11:E12')
sheet['D11'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D11'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[11].height = 21
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D11'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F11:I12')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=12, column=col).border = Border(bottom=bottom_line)
sheet['F11'].border = Border(bottom=bottom_line)
sheet['F11'].alignment = Alignment(horizontal='center', vertical='center')

# 软件版本
sheet['D14'] = '软 件 版 本：Software Rev.'
sheet.merge_cells('D14:E15')
sheet['D14'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D14'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[14].height = 21
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D14'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F14:I15')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=15, column=col).border = Border(bottom=bottom_line)
sheet['F14'].border = Border(bottom=bottom_line)
sheet['F14'].alignment = Alignment(horizontal='center', vertical='center')

# 制样部门
sheet['D17'] = '制 样 部 门：Make Team'
sheet.merge_cells('D17:E18')
sheet['D17'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D17'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[17].height = 21
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D17'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F17:I18')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=18, column=col).border = Border(bottom=bottom_line)
sheet['F17'].border = Border(bottom=bottom_line)
sheet['F17'].alignment = Alignment(horizontal='center', vertical='center')

# 检测部门
sheet['D20'] = '检 测 部 门：Test team'
sheet.merge_cells('D20:E21')
sheet['D20'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D20'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[20].height = 21
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D20'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F20:I21')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=21, column=col).border = Border(bottom=bottom_line)
sheet['F20'].border = Border(bottom=bottom_line)
sheet['F20'].alignment = Alignment(horizontal='center', vertical='center')

# 报告编号
sheet['D23'] = '报 告 编 号：Report No.'
sheet.merge_cells('D23:E24')
sheet['D23'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D23'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[23].height = 21
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D23'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F23:I24')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=24, column=col).border = Border(bottom=bottom_line)
sheet['F23'].border = Border(bottom=bottom_line)
sheet['F23'].alignment = Alignment(horizontal='center', vertical='center')

# 测试结果
sheet['D26'] = '测 试 结 果：Test Result'
sheet.merge_cells('D26:E27')
sheet['D26'].font = Font(name='微软雅黑', size='12', bold=True, italic=False, color=colors.BLACK)
sheet['D26'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[26].height = 21
sheet.column_dimensions['D'].width = 8.38
left, right, top, bottom = [Side(style='none')] * 4
sheet['D26'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F26:I27')
# 设置下划线
bottom_line = Side(style='thin', color='000000')
for col in range(6, 10):  # F=6, G=7, H=8, I=9
    sheet.cell(row=27, column=col).border = Border(bottom=bottom_line)
sheet['F26'].border = Border(bottom=bottom_line)
sheet['F26'].alignment = Alignment(horizontal='center', vertical='center')

# 作成者
sheet['C29'] = '作  成  者  Prepared by'
sheet.merge_cells('C29:E29')
sheet['C29'].font = Font(name='宋体', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['C29'].alignment = Alignment(horizontal='center', vertical='top', wrap_text=True)
sheet.row_dimensions[29].height = 39
sheet.column_dimensions['C'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['C29'].border = Border(left=left, right=right, top=top, bottom=bottom)

# 审核者
sheet['F29'] = '审  核  者   Checked by'
sheet.merge_cells('F29:H29')
sheet['F29'].font = Font(name='宋体', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['F29'].alignment = Alignment(horizontal='center', vertical='top', wrap_text=True)
sheet.row_dimensions[29].height = 39
sheet.column_dimensions['F'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['F29'].border = Border(left=left, right=right, top=top, bottom=bottom)

# 批准者
sheet['I29'] = '批  准  者  Approved by'
sheet.merge_cells('I29:K29')
sheet['I29'].font = Font(name='宋体', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['I29'].alignment = Alignment(horizontal='center', vertical='top', wrap_text=True)
sheet.row_dimensions[29].height = 39
sheet.column_dimensions['I'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['I29'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('C30:E31')
sheet['C30'].font = Font(name='微软雅黑', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['C30'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[30].height = 20.25
sheet.row_dimensions[31].height = 20.25
sheet.column_dimensions['C'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['C30'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F30:H31')
sheet['F30'].font = Font(name='微软雅黑', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['F30'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[30].height = 20.25
sheet.row_dimensions[31].height = 20.25
sheet.column_dimensions['F'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['F30'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('I30:K31')
sheet['I30'].font = Font(name='微软雅黑', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['I30'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet.row_dimensions[30].height = 20.25
sheet.row_dimensions[31].height = 20.25
sheet.column_dimensions['I'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['I30'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('C32:E32')
sheet['C32'].font = Font(name='微软雅黑', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['C32'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
sheet.row_dimensions[32].height = 19.95
sheet.column_dimensions['C'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['C32'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('F32:H32')
sheet['F32'].font = Font(name='微软雅黑', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['F32'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
sheet.row_dimensions[32].height = 19.95
sheet.column_dimensions['F'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['F32'].border = Border(left=left, right=right, top=top, bottom=bottom)

sheet.merge_cells('I32:K32')
sheet['I32'].font = Font(name='微软雅黑', size='14', bold=False, italic=False, color=colors.BLACK)
sheet['I32'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
sheet.row_dimensions[32].height = 19.95
sheet.column_dimensions['I'].width = 8.3
left, right, top, bottom = [Side(style='none')] * 4
sheet['I32'].border = Border(left=left, right=right, top=top, bottom=bottom)

# 创建边框样式（所有边框均为实线）
border_style = Border(
    top=Side(border_style="thin"),
    right=Side(border_style="thin"),
    bottom=Side(border_style="thin"),
    left=Side(border_style="thin")
)

# 为 C29 到 K32 范围内的每个单元格添加边框
for row in sheet['C29:K32']:
    for cell in row:
        cell.border = border_style

# 创建白色背景的填充样式
white_fill = PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')

# 将 A1 到 L35 范围内的每个单元格的背景颜色设置为白色
for row in sheet['A1:L35']:
    for cell in row:
        cell.fill = white_fill

# 保存
workbook.save(r'E:\Dding_file\testbms.xlsx')
